iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 15
1
Software Development

從問題理解與活用SQL語法系列 第 15 篇

第十五堂：電影資料 - 查詢綜合練習_實作過程

11th鐵人賽
631 瀏覽

  • 分享至 

  • xImage
    •  

題目資料庫：電影資料

https://www.w3resource.com/sql-exercises/movie-database-exercise/joins-exercises-on-movie-database.php

一、第一題

(一) 題目

請統計各年份的電影評分(rev_stars)的平均，並列出所有高於該年份的評分平均的電影當中，英國、非英國各有幾部

例如：1997年的電影平均比分為5.8分，評分高於平均的電影當中，英國有2部電影，非英國則有0部電影

| mov_year | average | uk_count | not_uk_count |
| ---------|---------------------|---------------|
| 1962 | 8.3 | 1 | 0 |
| 1982 | 8.2 | 1 | 0 |
| 1999 | 7.0 | 1 | 0 |
| 1996 | 0.0 | 0 | 0 |
| 2009 | 7.3 | 1 | 0 |
| 1986 | 8.4 | 1 | 0 |
| 2004 | 6.7 | 1 | 0 |
| 1974 | 0.0 | 0 | 0 |
| 1995 | 8.2 | 1 | 0 |
| 1961 | 7.9 | 0 | 1 |
| 1977 | 8.1 | 0 | 1 |
| 2001 | 8.1 | 1 | 0 |
| 1997 | 5.8 | 2 | 0 |
| 2008 | 8.0 | 1 | 0 |
| 1958 | 8.4 | 1 | 0 |

(二) 參考實作結果

SELECT mov_year,
       ROUND(AVG_result.average, 1) AS average,
       (
         SELECT COUNT(movie.mov_id)
         FROM movie
         INNER JOIN rating
         ON movie.mov_id = rating.mov_id
         WHERE rating.rev_stars >= AVG_result.average AND
               movie.mov_year = AVG_result.mov_year AND
               mov_rel_country = 'UK'
        ) AS UK_Count ,
        (
         SELECT COUNT(movie.mov_id)
         FROM movie
         INNER JOIN rating
         ON movie.mov_id = rating.mov_id
         WHERE rating.rev_stars >= AVG_result.average AND
               movie.mov_year = AVG_result.mov_year AND
               mov_rel_country <> 'UK'
        ) AS NOT_UK_Count
FROM (SELECT mov_year,
             (
              CASE
                WHEN AVG(rev_stars) IS NULL THEN 0
                ELSE AVG(rev_stars)
              END
             ) AS average
      FROM movie
      INNER JOIN rating
      ON movie.mov_id = rating.mov_id
      GROUP BY mov_year) AS AVG_result

(三) 思路過程

1.先在子查詢計算每一年的評分平均

FROM (SELECT mov_year,
             (
              CASE
                WHEN AVG(rev_stars) IS NULL THEN 0
                ELSE AVG(rev_stars)
              END
             ) AS average
      FROM movie
      INNER JOIN rating
      ON movie.mov_id = rating.mov_id
      GROUP BY mov_year) AS AVG_result
mov_year average
1962 8.3
1982 8.2
1999 7.0
1996 0.0
2009 7.3
1986 8.4
2004 6.7
1974 0.0
1995 8.2
1961 7.9
1977 8.1
2001 8.1
1997 5.8
2008 8.0
1958 8.4

2.將每一年算出來的平均帶入兩個SELECT子查詢，分別計算高於評分平均的英國電影與非英國電影各有幾部

SELECT mov_year,
       ROUND(AVG_result.average, 1) AS average,
       (
         SELECT COUNT(movie.mov_id)
         FROM movie
         INNER JOIN rating
         ON movie.mov_id = rating.mov_id
         WHERE rating.rev_stars >= AVG_result.average AND
               movie.mov_year = AVG_result.mov_year AND
               mov_rel_country = 'UK'
        ) AS UK_Count ,
        (
         SELECT COUNT(movie.mov_id)
         FROM movie
         INNER JOIN rating
         ON movie.mov_id = rating.mov_id
         WHERE rating.rev_stars >= AVG_result.average AND
               movie.mov_year = AVG_result.mov_year AND
               mov_rel_country <> 'UK'
        ) AS NOT_UK_Count

| mov_year | average | uk_count | not_uk_count |
| ---------|---------------------|---------------|
| 1962 | 8.3 | 1 | 0 |
| 1982 | 8.2 | 1 | 0 |
| 1999 | 7.0 | 1 | 0 |
| 1996 | 0.0 | 0 | 0 |
| 2009 | 7.3 | 1 | 0 |
| 1986 | 8.4 | 1 | 0 |
| 2004 | 6.7 | 1 | 0 |
| 1974 | 0.0 | 0 | 0 |
| 1995 | 8.2 | 1 | 0 |
| 1961 | 7.9 | 0 | 1 |
| 1977 | 8.1 | 0 | 1 |
| 2001 | 8.1 | 1 | 0 |
| 1997 | 5.8 | 2 | 0 |
| 2008 | 8.0 | 1 | 0 |
| 1958 | 8.4 | 1 | 0 |

ROUND(數字, 1) =>將數字四捨五入到小數點第1位

二、第二題

(一) 題目

請分別統計2000年以前(不包含2000年)與2000年以後(包含2000年)
片長低於該區間的平均時數的所有電影當中，片長前3長的的影片導演、影片名稱、影片年分、影片時間、所屬的區間影片平均時數、排名結果

例如：

  1. 電影 Slumdog Millionaire，是2000年以前的電影當中，低於所有2000年以前的電影的平均時數128，當中片長第1長的電影
  2. 電影 Hayao Miyazaki，是2000年以後的電影當中，低於所有2000年以後的電影的平均時數141，當中片長第2長的電影
category director_fullname mov_title mov_year mov_time average_time rank
AFTER 2000 Below time average Rank Danny Boyle Slumdog Millionaire 2008 120 128 1
AFTER 2000 Below time average Rank Kevin Spacey Beyond the Sea 2004 118 128 2
AFTER 2000 Below time average Rank Richard Kelly Donnie Darko 2001 113 128 3
BEFORE 2000 Below time average Rank James Cameron Aliens 1986 137 141 1
BEFORE 2000 Below time average Rank Hayao Miyazaki Princess Mononoke 1997 134 141 2
BEFORE 2000 Below time average Rank Roman Polanski Chinatown 1974 130 141 3

(二) 參考實作結果

SELECT *
FROM (SELECT 'AFTER 2000 Below time average Rank' AS Category,
              CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
              movie.mov_title,
              movie.mov_year,
              movie.mov_time,
              avg_before2000.average_time,
              RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
      FROM  movie
      INNER JOIN movie_direction
      ON movie.mov_id = movie_direction.mov_id
      INNER JOIN director
      ON movie_direction.dir_id = director.dir_id 
      INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
                  FROM movie
                  WHERE mov_year >= 2000 ) AS avg_before2000
      ON 1=1
      WHERE mov_time <= avg_before2000.average_time AND
            mov_year >= 2000
      UNION (SELECT 'BEFORE 2000 Below time average Rank' AS Category,
                     CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
                     movie.mov_title,
                     movie.mov_year,
                     movie.mov_time,
                     avg_before2000.average_time,
                     RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
             FROM  movie
             INNER JOIN movie_direction
             ON movie.mov_id = movie_direction.mov_id
             INNER JOIN director
             ON movie_direction.dir_id = director.dir_id 
             INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
                         FROM movie
                         WHERE mov_year < 2000 ) AS avg_before2000
             ON 1=1
             WHERE mov_time <= avg_before2000.average_time AND
                   mov_year < 2000)) AS All_Rank
WHERE rank <= 3
ORDER BY mov_year,
         rank

(三) 思路過程

1.　分別算出前「2000年以前」與「2000年以後」所有低於電影平均時間長

2000年以後：

SELECT ROUND(AVG(mov_time)) AS average_time
FROM movie
WHERE mov_year >= 2000
average_time
128

2000年以前：

SELECT ROUND(AVG(mov_time)) AS average_time
FROM movie
WHERE mov_year < 2000
average_time
141

2.　分別找出「2000年以前」與「2000年以後」所有低於時間平均長的「電影名稱 (movie.mov_title)」、「電影年份(movie.mov_year)」、「電影時間長」，使用Cross JOIN 將電影平均時間長與每一筆資料合併

2000年以後：

SELECT  movie.mov_title,
        movie.mov_year,
        movie.mov_time,
        avg_before2000.average_time
FROM  movie
INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
            FROM movie
            WHERE mov_year >= 2000 ) AS avg_before2000
ON 1=1
WHERE mov_time <= avg_before2000.average_time AND
      mov_year >= 2000
mov_title mov_year mov_time average_time
Donnie Darko 2001 113 128
Slumdog Millionaire 2008 120 128
Beyond the Sea 2004 118 128
Spirited Away 2001 125 128

2000年以前：

SELECT  movie.mov_title,
        movie.mov_year,
        movie.mov_time,
        avg_before2000.average_time
FROM  movie
INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
            FROM movie
            WHERE mov_year < 2000 ) AS avg_before2000
ON 1=1
WHERE mov_time <= avg_before2000.average_time AND
      mov_year < 2000
mov_title mov_year mov_time average_time
Vertigo 1958 128 141
The Innocents 1961 100 141
Blade Runner 1982 117 141
The Usual Suspects 1995 106 141
Chinatown 1974 130 141
Annie Hall 1977 93 141
Princess Mononoke 1997 134 141
American Beauty 1999 122 141
Good Will Hunting 1997 126 141
Deliverance 1972 109 141
Trainspotting 1996 94 141
Aliens 1986 137 141
Back to the Future 1985 116 141

3. 使用INNER JOIN關聯「movie_direction」跟「director」後，使用CONCAT合併導演的fname和lname得到導演的全名。使用RANK函數，依據電影時間長(movie.mov_time)，使用OVER 子句由長到短排序後，給予排名

2000年之後：

SELECT 'AFTER 2000 Below time average Rank' AS Category,
        CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
        movie.mov_title,
        movie.mov_year,
        movie.mov_time,
        avg_before2000.average_time,
        RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
FROM  movie
INNER JOIN movie_direction
ON movie.mov_id = movie_direction.mov_id
INNER JOIN director
ON movie_direction.dir_id = director.dir_id 
INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
            FROM movie
            WHERE mov_year >= 2000 ) AS avg_before2000
ON 1=1
WHERE mov_time <= avg_before2000.average_time AND
      mov_year >= 2000
category director_fullname mov_title mov_year mov_time average_time rank
AFTER 2000 Below time average Rank Danny Boyle Slumdog Millionaire 2008 120 128 1
AFTER 2000 Below time average Rank Kevin Spacey Beyond the Sea 2004 118 128 2
AFTER 2000 Below time average Rank Richard Kelly Donnie Darko 2001 113 128 3

2000年以前

SELECT 'BEFORE 2000 Below time average Rank' AS Category,
        CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
        movie.mov_title,
        movie.mov_year,
        movie.mov_time,
        avg_before2000.average_time,
        RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
 FROM  movie
 INNER JOIN movie_direction
 ON movie.mov_id = movie_direction.mov_id
 INNER JOIN director
 ON movie_direction.dir_id = director.dir_id 
 INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
             FROM movie
             WHERE mov_year < 2000 ) AS avg_before2000
 ON 1=1
 WHERE mov_time <= avg_before2000.average_time AND
       mov_year < 2000
category director_fullname mov_title mov_year mov_time average_time rank
BEFORE 2000 Below time average Rank James Cameron Aliens 1986 137 141 1
BEFORE 2000 Below time average Rank Hayao Miyazaki Princess Mononoke 1997 134 141 2
BEFORE 2000 Below time average Rank Roman Polanski Chinatown 1974 130 141 3
BEFORE 2000 Below time average Rank Alfred Hitchcock Vertigo 1958 128 141 4
BEFORE 2000 Below time average Rank Gus Van Sant Good Will Hunting 1997 126 141 5
BEFORE 2000 Below time average Rank Sam Mendes American Beauty 1999 122 141 6
BEFORE 2000 Below time average Rank Ridley Scott Blade Runner 1982 117 141 7
BEFORE 2000 Below time average Rank John Boorman Deliverance 1972 109 141 8
BEFORE 2000 Below time average Rank Bryan Singer The Usual Suspects 1995 106 141 9
BEFORE 2000 Below time average Rank Jack Clayton The Innocents 196 1 100 141 10
BEFORE 2000 Below time average Rank Danny Boyle Trainspotting 1996 94 141 11
BEFORE 2000 Below time average Rank Woody Allen Annie Hall 1977 93 141 12

4. 兩邊分別取前三名，最後使用UNION聯集運算合併「2000年以前」與「2000年以後」的資料，依照分類分別從第1名排到第3名排序下來

SELECT *
FROM (SELECT 'AFTER 2000 Below time average Rank' AS Category,
              CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
              movie.mov_title,
              movie.mov_year,
              movie.mov_time,
              avg_before2000.average_time,
              RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
      FROM  movie
      INNER JOIN movie_direction
      ON movie.mov_id = movie_direction.mov_id
      INNER JOIN director
      ON movie_direction.dir_id = director.dir_id 
      INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
                  FROM movie
                  WHERE mov_year >= 2000 ) AS avg_before2000
      ON 1=1
      WHERE mov_time <= avg_before2000.average_time AND
            mov_year >= 2000
      UNION (SELECT 'BEFORE 2000 Below time average Rank' AS Category,
                     CONCAT(director.dir_fname, ' ', director.dir_lname) AS director_fullname,
                     movie.mov_title,
                     movie.mov_year,
                     movie.mov_time,
                     avg_before2000.average_time,
                     RANK() OVER(ORDER BY movie.mov_time DESC) AS Rank
             FROM  movie
             INNER JOIN movie_direction
             ON movie.mov_id = movie_direction.mov_id
             INNER JOIN director
             ON movie_direction.dir_id = director.dir_id 
             INNER JOIN (SELECT ROUND(AVG(mov_time)) AS average_time
                         FROM movie
                         WHERE mov_year < 2000 ) AS avg_before2000
             ON 1=1
             WHERE mov_time <= avg_before2000.average_time AND
                   mov_year < 2000)) AS All_Rank
WHERE rank <= 3
ORDER BY category,
         rank
category director_fullname mov_title mov_year mov_time average_time rank
AFTER 2000 Below time average Rank Danny Boyle Slumdog Millionaire 2008 120 128 1
AFTER 2000 Below time average Rank Kevin Spacey Beyond the Sea 2004 118 128 2
AFTER 2000 Below time average Rank Richard Kelly Donnie Darko 2001 113 128 3
BEFORE 2000 Below time average Rank James Cameron Aliens 1986 137 141 1
BEFORE 2000 Below time average Rank Hayao Miyazaki Princess Mononoke 1997 134 141 2
BEFORE 2000 Below time average Rank Roman Polanski Chinatown 1974 130 141 3

上一篇
第十四堂：電影資料 - 查詢綜合練習
下一篇
第十六堂：醫院資料庫 - 非SELECT查詢綜合練習時間
系列文
從問題理解與活用SQL語法 共 30 篇
  1. 26
    第二十六堂：英文單字測驗程式 - 學生帳號資料管理(5) - 修改學生與刪除學生 (認識實體)
  2. 27
    第二十七堂：英文單字測驗程式 - 學生帳號資料管理(6) - 物件實體操作資料 (ORM、Entity Framework)
  3. 28
    第二十八堂：英文單字測驗程式 - 單字測驗 - 亂數取得單字 (LIMIT、TOP、綜合練習)
  4. 29
    第二十九堂：英文單字測驗程式 - 測驗主明細紀錄與正確率統計 (綜合練習)
  5. 30
    第三十堂：師父引進門，分手在個人 (專案開源+從與SQL的分分合合到現在開課誤人子弟)
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙